using System;
using System.Data;
using System.Data.SqlClient;
using IFXplus.SmartStorage.Settings;
using IFXplus.SmartStorage.Utils;

namespace IFXplus.SmartStorage.Providers
{
    internal class SqlServerStorageProvider : IStorageProvider
    {
        #region Privates

        private readonly StorageProviderReference _reference = null;
        private readonly SqlServerStorageProviderSettings _settings = null;

        private string _tableName = null;
        private string _storedProcedureName_Get = null;
        private string _storedProcedureName_Insert = null;
        private string _storedProcedureName_Remove = null;
        
        #endregion

        #region Constructor

        public SqlServerStorageProvider(StorageProviderReference reference)
        {
            if (reference == null)
                throw new ArgumentNullException("reference");
            if (string.IsNullOrEmpty(reference.Id))
                throw new ArgumentException("reference.Id cannot be null or empty", "reference");
            _reference = reference;
            _settings = StorageSettings.GetInstance().GetStorageProviderSettings<SqlServerStorageProviderSettings>(reference.Id);
            if (_settings == null)
                throw new StorageSettingsException(string.Format("{0} provider settings not found", reference));

            InitDatabase();
        }

        #endregion

        #region Properties

        private SqlServerStorageProviderSettings Settings
        {
            get { return _settings; }
        }

        private string TableName
        {
            get
            {
                if (string.IsNullOrEmpty(_tableName))
                    _tableName = Settings.TableName.Replace("'", "''"); // avoiding Sql Injection attacks
                return _tableName;
            }
        }

        private string StoredProcedureName_Get
        {
            get
            {
                if (string.IsNullOrEmpty(_storedProcedureName_Get))
                    _storedProcedureName_Get = "sp_" + TableName + "_Get";
                return _storedProcedureName_Get;
            }
        }

        private string StoredProcedureName_Insert
        {
            get
            {
                if (string.IsNullOrEmpty(_storedProcedureName_Insert))
                    _storedProcedureName_Insert = "sp_" + TableName + "_Insert";
                return _storedProcedureName_Insert;
            }
        }

        private string StoredProcedureName_Remove
        {
            get
            {
                if (string.IsNullOrEmpty(_storedProcedureName_Remove))
                    _storedProcedureName_Remove = "sp_" + TableName + "_Remove";
                return _storedProcedureName_Remove;
            }
        }

        #endregion

        #region IStorageProvider Members

        public StorageProviderReference Reference
        {
            get { return _reference; }
        }

        public void Insert(StorageEntry entry)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(StoredProcedureName_Insert, cn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@seKey", entry.Key);
                        cmd.Parameters.AddWithValue("@seExpirationTime",
                                                    (entry.DefaultAbsoluteTTL != StorageItemMetadata.NoAbsoluteTTL)
                                                        ? entry.CreationTime.Add(entry.DefaultAbsoluteTTL)
                                                        : DBNull.Value as object);
                        cmd.Parameters.AddWithValue("@seValue", Base64Serializer.Serialize(entry));
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new StorageException(string.Format("Exception in SqlServerStorageProvider.Insert({0})", entry.Key), ex);
            }
        }

        public void Remove(string key)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(StoredProcedureName_Remove, cn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@seKey", key);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new StorageException(string.Format("Exception in SqlServerStorageProvider.Remove({0})", key), ex);
            }
        }

        public StorageEntry Get(string key)
        {
            StorageEntry entry = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(StoredProcedureName_Get, cn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@seKey", key);
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            if (dr.Read())
                            {
                                string base64Entry = dr["seValue"].ToString();
                                if (!string.IsNullOrEmpty(base64Entry))
                                    entry = (StorageEntry) Base64Serializer.Deserialize(base64Entry);
                            }
                        }
                    }
                }
                return entry;
            }
            catch (Exception ex)
            {
                throw new StorageException(string.Format("Exception in SqlServerStorageProvider.Get({0})", key), ex);
            }           
        }

        #endregion

        #region Init database

        private void InitDatabase()
        {
            try
            {
                string sql =
                            "SET QUOTED_IDENTIFIER ON \r\n" +
                            "IF NOT EXISTS (SELECT object_id FROM sys.objects WHERE object_id = OBJECT_ID(N'" + TableName + "') AND type in (N'U')) \r\n" +
                            "BEGIN \r\n" +
                            "    CREATE TABLE [" + TableName + "] \r\n" +
                            "    ( \r\n" +
                            "        [seKey] [nvarchar](" + StorageSettings.MAX_KEY_LENGTH + ") NOT NULL, \r\n" +
                            "        [seExpirationTime] [datetime] NULL, \r\n" +
                            "        [seValue] [ntext] NOT NULL, \r\n" +
                            "        CONSTRAINT [PK_" + TableName + "] PRIMARY KEY CLUSTERED ([seKey] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] \r\n" +
                            "    ) \r\n" +
                            "    ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] \r\n" +
                            "END \r\n" +

                            "SET QUOTED_IDENTIFIER ON \r\n" +
                            "--GO \r\n" +
                            "IF NOT EXISTS (SELECT object_id FROM sys.objects WHERE object_id = OBJECT_ID(N'" + StoredProcedureName_Get + "') AND type in (N'P', N'PC')) \r\n" +
                            "BEGIN \r\n" +
                            "    EXEC sp_executesql @statement = N' \r\n" +
                            "    CREATE PROCEDURE " + StoredProcedureName_Get + " \r\n" +
                            "        @seKey nvarchar(" + StorageSettings.MAX_KEY_LENGTH + ") = '''' \r\n" +
                            "    AS \r\n" +
                            "    BEGIN \r\n" +
                            "        SELECT seValue FROM " + TableName + " WHERE seKey=@seKey \r\n" +
                            "    END'  \r\n" +
                            "END \r\n" +

                            "SET QUOTED_IDENTIFIER ON \r\n" +
                            "IF NOT EXISTS (SELECT object_id FROM sys.objects WHERE object_id = OBJECT_ID(N'" + StoredProcedureName_Insert + "') AND type in (N'P', N'PC')) \r\n" +
                            "BEGIN \r\n" +
                            "    EXEC sp_executesql @statement = N' \r\n" +
                            "    CREATE PROCEDURE " + StoredProcedureName_Insert + " \r\n" +
                            "        @seKey nvarchar(" + StorageSettings.MAX_KEY_LENGTH + ") = '''',  \r\n" +
                            "        @seExpirationTime datetime = null, \r\n" +
                            "        @seValue ntext = '''' " +
                            "    AS \r\n" +
                            "    IF NOT EXISTS(SELECT seKey FROM " + TableName + " WHERE seKey=@seKey) \r\n" +
                            "        BEGIN \r\n" +
                            "            INSERT INTO " + TableName + " (seKey, seExpirationTime, seValue) VALUES (@seKey, @seExpirationTime, @seValue) \r\n" +
                            "        END \r\n" +
                            "    ELSE \r\n" +
                            "        BEGIN \r\n" +
                            "            UPDATE " + TableName + " SET seExpirationTime = @seExpirationTime, seValue = @seValue WHERE seKey=@seKey \r\n" +
                            "        END'  \r\n" +
                            "END \r\n" +

                            "SET QUOTED_IDENTIFIER ON \r\n" +
                            "IF NOT EXISTS (SELECT object_id FROM sys.objects WHERE object_id = OBJECT_ID(N'" + StoredProcedureName_Remove + "') AND type in (N'P', N'PC')) \r\n" +
                            "BEGIN \r\n" +
                            "    EXEC sp_executesql @statement = N' \r\n" +
                            "    CREATE PROCEDURE " + StoredProcedureName_Remove + " \r\n" +
                            "        @seKey nvarchar(" + StorageSettings.MAX_KEY_LENGTH + ") = '''' \r\n" +
                            "    AS \r\n" +
                            "        BEGIN \r\n" +
                            "           DELETE FROM " + TableName + " WHERE seKey = @seKey OR seExpirationTime < GETDATE() \r\n" +
                            "        END' \r\n" +
                            "END \r\n";
                using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }      
            }
            catch(Exception ex)
            {
                throw new StorageException(string.Format("Exception in SqlServerStorageProvider.InitDatabase({0})", Settings.Id), ex);
            }            
        }

        #endregion
    }
}
